﻿using System;
using System.Collections.Specialized;
using System.Collections.Generic;
using System.Data.Objects;
using System.Data.Common;
using System.Data.SqlClient;
using System.Data.EntityClient;
using System.Linq;
using System.Linq.Dynamic;
using System.Text;
using Ashlen.Portal.Core.DataInterfaces;
using Ashlen.Portal.Core;
using Ashlen.Portal.Core.DTO;
using System.Configuration;
using System.Transactions;

namespace Ashlen.Portal.Data.SqlServer
{
    public class WidgetDao : BaseDao, IWidgetDao
    {
        #region ctor
        public WidgetDao() : base()
        {
        }

        public WidgetDao(string connectionString)
            : base(connectionString)
        {
        }
        #endregion

        #region Singleton Instance

        private static IWidgetDao _instance;

        public static IWidgetDao Instance {
            get {
                if (_instance == null) {
                    _instance = new WidgetDao();
                }
                return _instance;
            }
        }

        #endregion

        #region IWidgetUIDao Members

        public object SelectWidget(Guid? id, string name) {
            RenewContext();
            var query = from w in Context.ui_Widget
                        where ( (name == null || name == default(string)) || w.Name == name) 
                        && (!id.HasValue || w.ID == id.Value)
                        select w;
            return query.FirstOrDefault();
        }

        public object SelectWidgets(Guid pageId) {
            RenewContext();
            var query = from wi in Context.ui_WidgetInstance.Include("ui_Widget")
                        where wi.ui_Page.ID == pageId
                        orderby wi.OrderNo ascending
                        select wi;
            return query.ToList();
        }

        public object SelectWidgets() {
            RenewContext();
            var query = from w in Context.ui_Widget
                        orderby w.OrderNo ascending
                        select w;
            return query.ToList();
        }

        public Guid CreateWidget(object oWidget) {
            ui_Widget widget = oWidget as ui_Widget;
            return CreateWidget(widget.Name, widget.Url, widget.Description, widget.VersionNo, widget.Author, widget.IsDefault,
                widget.Body, widget.DefaultState, widget.Icon, widget.OrderNo, widget.UserCreated);
        }

        public Guid CreateWidget(string name, string url, string desc, int version, string author, bool isDefault, 
                                string body, string defaultState, string icon, int orderNo, string userCreated) {

            RenewContext();
            var widget = new ui_Widget();
            Guid newId = Guid.NewGuid();

            widget.ID = newId;
            widget.Name = name;
            widget.Url = url;
            widget.Description = desc;
            widget.VersionNo = version;
            widget.Author = author;
            widget.IsDefault = isDefault;
            widget.Body = body;
            widget.DefaultState = defaultState;
            widget.Icon = icon;
            widget.OrderNo = orderNo;
            widget.UserCreated = userCreated;
            widget.DateCreated = DateTime.Now;

            Context.AddToui_Widget(widget);
            Context.SaveChanges();

            return newId;
        }

        public void UpdateWidget(object oWidget) {
            ui_Widget widget = oWidget as ui_Widget;
            UpdateWidget(widget.ID, widget.Name, widget.Url, widget.Description, widget.VersionNo, widget.Author,
                widget.IsDefault, widget.Body, widget.DefaultState, widget.Icon, widget.OrderNo, widget.UserUpdated);
        }

        public void UpdateWidget(Guid id, string name, string url, string desc, int version, string author, bool isDefault, string body, string defaultState, string icon, int orderNo, string userUpdated) {
            RenewContext();
            var widget = Context.ui_Widget.FirstOrDefault(w => w.ID == id);
            if (widget != null) {
                widget.Name = name;
                widget.Url = url;
                widget.Description = desc;
                widget.VersionNo = version;
                widget.Author = author;
                widget.IsDefault = isDefault;
                widget.Body = body;
                widget.DefaultState = defaultState;
                widget.Icon = icon;
                widget.OrderNo = orderNo;
                widget.UserUpdated = userUpdated;
                widget.DateUpdated = DateTime.Now;
            }
            Context.SaveChanges();
        }

        public void DeleteWidget(Guid id) {
            RenewContext();
            var widget = Context.ui_Widget.FirstOrDefault(w => w.ID == id);
            if (widget != null) {
                Context.DeleteObject(widget);
                Context.SaveChanges();
            }
        }

        public object SelectWidgetInstance(Guid? widgetId, Guid? pageId) {
            RenewContext();
            var query = from wi in Context.ui_WidgetInstance
                        where ((!widgetId.HasValue || wi.ui_Widget.ID == widgetId.Value) && 
                        (!pageId.HasValue || wi.ui_Page.ID == pageId.Value))
                        select wi;
            //return query.Select(wi => new { wi.Id, wi.ColumnNo, wi.OrderNo, wi.Expanded, wi.Title, wi.State, wi.VersionNo, wi.UserCreated, wi.UserUpdated,
            //wi.DateCreated, wi.DateUpdated});
            return query.FirstOrDefault();
        }

        public Guid CreateWidgetInstance(Guid widgetId, Guid pageId, int colNo, int orderNo, bool isExpanded, string title, string state, int version, string userCreated) {
            RenewContext();
            var wi = new ui_WidgetInstance();
            var page = Context.ui_Page.FirstOrDefault(p => p.ID == pageId);
            if (page == null) { throw new ArgumentException(string.Format("Page (id: {0}) does not exist.", pageId)); }
            var widget = Context.ui_Widget.FirstOrDefault(w => w.ID == widgetId);
            if (widget == null) { throw new ArgumentException(string.Format("Widget (id: {0}) does not exist.", widgetId)); }

            wi.Id = Guid.NewGuid();
            wi.ui_Page = page;
            wi.ui_Widget = widget;
            wi.ColumnNo = colNo;
            wi.OrderNo = orderNo;
            wi.Expanded = isExpanded;
            wi.Title = title;
            wi.State = state;
            wi.VersionNo = version;
            wi.UserCreated = userCreated;
            wi.DateCreated = DateTime.Now;

            Context.AddToui_WidgetInstance(wi);
            Context.SaveChanges();
            return wi.Id;
        }

        public void UpdateWidgetInstance(Guid id, Guid widgetId, Guid pageId, int colNo, int orderNo, bool isExpanded, string title, string state, int version,
            string userUpdated) {
            RenewContext();
            var widgetInstance = Context.ui_WidgetInstance.Include("ui_Page").Include("ui_Widget").First(w => w.Id == id);
            if (widgetInstance == null) {
                throw new ArgumentException(string.Format("The widget instance (id={0}) does not exist.", id));
            }

            if (widgetInstance.ui_Widget.ID != widgetId) {
                var widget = Context.ui_Widget.FirstOrDefault(w => w.ID == widgetId);
                if (widget == null) {
                    throw new ArgumentException(string.Format("Widget id {0} not found for update of widget instance {1}", widgetId, id));
                }
                widgetInstance.ui_Widget = widget;
            }

            if (widgetInstance.ui_Page.ID != pageId) {
                var page = Context.ui_Page.FirstOrDefault(p => p.ID == pageId);
                if (page == null) {
                    throw new ArgumentException(string.Format("Page id {0} not found for update of widget instance {1}", pageId, id));
                }
                widgetInstance.ui_Page = page;
            }

            widgetInstance.ColumnNo = colNo;
            widgetInstance.OrderNo = orderNo;
            widgetInstance.Expanded = isExpanded;
            widgetInstance.Title = title;
            widgetInstance.State = state;
            widgetInstance.VersionNo = version;
            widgetInstance.UserUpdated = userUpdated;
            widgetInstance.DateUpdated = DateTime.Now;

            Context.SaveChanges();
        }

        public void DeleteWidgetInstance(Guid id) {
            RenewContext();
            var widgetInstance = Context.ui_WidgetInstance.FirstOrDefault(wi => wi.Id == id);
            if (widgetInstance != null) {
                Context.DeleteObject(widgetInstance);
                Context.SaveChanges();
            }
        }

        public object SelectPages(string username) {
            RenewContext();
            Guid userId = new Guid();
            aspnet_Users users = Context.aspnet_Users.FirstOrDefault(u => u.UserName == username);
            if (users != null) {
                userId = users.UserId;
                var query = from p in Context.ui_Page.Include("ui_Layout")
                            where (p.UserId == userId)
                            select p;
                return query.ToList();
            }
            return null;
        }

        public object SelectPage(string title, string username) {
            RenewContext();
            Guid userId = Context.aspnet_Users.FirstOrDefault(u => u.UserName == username).UserId;
            var query = from p in Context.ui_Page.Include("ui_Layout")
                        where (p.Title == title && p.UserId == userId) 
                        select p;
            return query.FirstOrDefault();
        }

        public Guid CreatePage(object oPage) {
            ui_Page page = oPage as ui_Page;
            //return CreatePage(page.Title, page.UserId, page.VersionNo, page.ui_Layout.Id, page.UserCreated);
            return new Guid();
        }

        public Guid CreatePage(string title, Guid userId, int version, int layoutType, string userCreated) {
            using (var t = new TransactionScope(TransactionScopeOption.Required,
                    new TransactionOptions { IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted })) {
                RenewContext();
                var page = new ui_Page();
                page.ID = Guid.NewGuid();
                page.Title = title;
                page.UserId = userId;
                page.VersionNo = version;
                //page.ui_Layout = Context.ui_Layout.FirstOrDefault(c => c.Id == layoutType);
                page.UserCreated = userCreated;
                page.DateCreated = DateTime.Now;

                Context.AddToui_Page(page);
                Context.SaveChanges();
                return page.ID;
            }
        }

        public object CreatePage(string title, string username, int version, int layoutType, string userCreated) {
            
            var userId = new Guid();
            //var layout = new ui_Layout();
            RenewContext();
            using (var t = new TransactionScope(TransactionScopeOption.Required,
                   new TransactionOptions { IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted })) {
                //orderNo = Context.ui_Page.Max(p => p.Order); orderNo++;
            }
            RenewContext();
            using (var t = new TransactionScope(TransactionScopeOption.Required,
                   new TransactionOptions { IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted })) {
                userId = Context.aspnet_Users.FirstOrDefault(u => u.UserName == username).UserId;
            }                  
            RenewContext();
            var page = new ui_Page();
            page.ID = Guid.NewGuid();
            page.Title = title;
            page.UserId = userId;
            page.VersionNo = version;
            //page.ui_Layout = Context.ui_Layout.FirstOrDefault(c => c.Id == layoutType);
            page.UserCreated = userCreated;
            page.DateCreated = DateTime.Now;
            //page.Order = orderNo;

            Context.AddToui_Page(page);
            Context.SaveChanges();
            return page;
        }

        public void UpdatePage(object oPage) {
            ui_Page page = oPage as ui_Page;
            //page.ui_Layout.Id, 
            UpdatePage(page.ID, page.Title, page.UserId, 1, page.VersionNo, page.UserUpdated);
        }

        public void UpdatePage(Guid id, string title, Guid userId, int version, int layoutType, string userUpdated) {
            RenewContext();
            var page = Context.ui_Page.FirstOrDefault(p => p.ID == id);
            if (page == null) {
                throw new ArgumentException(string.Format("Page id: {0} not found.", id));
            }
            page.Title = title;
            page.UserId = userId;
            page.VersionNo = version;
            //page.ui_Layout = Context.ui_Layout.FirstOrDefault(c => c.Id == layoutType);
            page.UserUpdated = userUpdated;
            page.DateUpdated = DateTime.Now;
            Context.SaveChanges();
        }

        public void UpdatePage(Guid id, string title, string username, int layoutId) {
            RenewContext();
            var page = Context.ui_Page.FirstOrDefault(p => p.ID == id);
            if (page == null) {
                throw new ArgumentException(string.Format("Page id: {0} not found.", id));
            }
            page.Title = title;
            page.UserUpdated = username;
            page.DateUpdated = DateTime.Now;
            //page.ui_Layout = Context.ui_Layout.FirstOrDefault(c => c.Id == layoutId);

            Context.SaveChanges();
        }


        public void DeletePage(Guid id) {
            RenewContext();
            var page = Context.ui_Page.FirstOrDefault(p => p.ID == id);
            if (page != null) {
                Context.DeleteObject(page);
            }
            Context.SaveChanges();
        }

        #endregion
    }
}
